#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class ArticleCategoryDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new ArticleCategory instance filled with the DataReader's current record data 
	     /// </summary>
	     private ArticleCategory GetArticleCategoryFromReader(IDataReader dr)
	     {
		     ArticleCategory obj = new ArticleCategory();
		     obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue :  (Int32)dr["CateID"];
		     obj.AppKey = (dr["AppKey"] is DBNull) ? String.Empty :  (String)dr["AppKey"];
		     obj.CateName = (dr["CateName"] is DBNull) ? String.Empty :  (String)dr["CateName"];
		     obj.CateName_en = (dr["CateName_en"] is DBNull) ? String.Empty :  (String)dr["CateName_en"];
		     obj.CateDesc = (dr["CateDesc"] is DBNull) ? String.Empty :  (String)dr["CateDesc"];
		     obj.ImageUrl = (dr["ImageUrl"] is DBNull) ? String.Empty :  (String)dr["ImageUrl"];
		     obj.CateOrder = (dr["CateOrder"] is DBNull) ? Int32.MinValue :  (Int32)dr["CateOrder"];
		     obj.ParentID = (dr["ParentID"] is DBNull) ? Int32.MinValue :  (Int32)dr["ParentID"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of ArticleCategory with the data read from the input DataReader
	     /// </summary>
	     private List<ArticleCategory> GetArticleCategoryCollectionFromReader(IDataReader dr)
	     {
		     List<ArticleCategory> obj = new List<ArticleCategory>();
		     while(dr.Read())
			     obj.Add(GetArticleCategoryFromReader(dr));
		     return obj;
	     }


         // lay nhung loai cong viec hien thi o trang chu
         public List<ArticleCategory> GetArticleCategoryHomePageByParentID(Int32 ParentID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("ArticleCategory_GetArticleCategoryHomePageByParentID", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetArticleCategoryCollectionFromReader(dr);
             }
         }


	     public int Insert(ArticleCategory obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("ArticleCategory_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = GetAutoCateID();
		     myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
		     myCommand.Parameters.Add("@CateName", SqlDbType.NVarChar).Value = obj.CateName;
		     myCommand.Parameters.Add("@CateName_en", SqlDbType.NVarChar).Value = obj.CateName_en;
		     myCommand.Parameters.Add("@CateDesc", SqlDbType.NVarChar).Value = obj.CateDesc;
		     myCommand.Parameters.Add("@ImageUrl", SqlDbType.VarChar).Value = obj.ImageUrl;
		     myCommand.Parameters.Add("@CateOrder", SqlDbType.Int).Value = obj.CateOrder;
		     myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = obj.ParentID;

		     return (int)myCommand.ExecuteNonQuery();
		     }
	     }

	     public bool Update(ArticleCategory obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("ArticleCategory_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
		     myCommand.Parameters.Add("@CateName", SqlDbType.NVarChar).Value = obj.CateName;
		     myCommand.Parameters.Add("@CateName_en", SqlDbType.NVarChar).Value = obj.CateName_en;
		     myCommand.Parameters.Add("@CateDesc", SqlDbType.NVarChar).Value = obj.CateDesc;
		     myCommand.Parameters.Add("@ImageUrl", SqlDbType.VarChar).Value = obj.ImageUrl;
		     myCommand.Parameters.Add("@CateOrder", SqlDbType.Int).Value = obj.CateOrder;
		     myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = obj.ParentID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     public bool Delete(Int32 CateID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("ArticleCategory_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the ArticleCategory with the specified ID
	     /// </summary>
	     public ArticleCategory GetArticleCategoryByID(Int32 CateID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("ArticleCategory_GetArticleCategoryByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetArticleCategoryFromReader(dr) : null);
		     }
	     }

	     //Get All ArticleCategory
	     public List<ArticleCategory> GetAllArticleCategory(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("ArticleCategory_GetAllArticleCategory", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetArticleCategoryCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  ArticleCategory
	     public int GetCountArticleCategory()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("ArticleCategory_GetCountArticleCategory", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

         public Int32 GetAutoCateID()
         {
             Int32 value;
             try
             {
                 using (SqlConnection myConn = GetConnectionString())
                 {
                     SqlCommand myCommand = new SqlCommand("ArticleCategory_GetAutoCateID", myConn);
                     myCommand.CommandType = CommandType.StoredProcedure;

                     value = ((Int32)myCommand.ExecuteScalar() + 1);
                     if (value == 1)
                         value = 2; // vi khong de trung voi ParentID = 1

                     return value;
                 }
             }
             catch { return 2; }
         }


         public List<ArticleCategory> GetArticleCategoryByParentID(Int32 ParentID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("ArticleCategory_GetArticleCategoryByParentID", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetArticleCategoryCollectionFromReader(dr);
             }
         }


	 }
}
